<?php

/**
 * This is the model class for table "tbl_inventory".
 *
 * The followings are the available columns in table 'tbl_inventory':
 * @property string $partno
 * @property string $description
 * @property string $type
 * @property string $cat
 * @property string $subcat
 * @property double $averagecost
 * @property double $pricea
 * @property double $priceb
 * @property double $pricec
 * @property double $lastprice
 * @property string $cunits
 * @property double $buysellr
 * @property string $markupcode
 * @property string $runits
 * @property string $costdebit
 * @property string $costcredit
 * @property string $salesdebit
 * @property string $salescredit
 * @property string $bin
 * @property string $pricebook
 * @property string $parttype
 * @property integer $posthist
 * @property string $vendor
 * @property string $salestype
 * @property string $notes
 * @property double $billrate
 * @property integer $nontaxable
 * @property string $epatype
 * @property string $jobclass
 * @property string $fr
 * @property integer $equipment
 * @property double $wamonths
 * @property string $aisle
 * @property string $histcode
 * @property string $mfg
 * @property string $model
 * @property double $reorderquanity
 * @property double $reorderlevel
 * @property string $refrtype
 * @property double $bcost
 * @property double $overhead
 * @property double $frplabor
 * @property integer $inactive
 * @property integer $ismarkup
 * @property string $imagepath
 * @property string $equipmenttype
 * @property double $baseprice
 */
class Inventory extends CActiveRecord {

    public $file;
    public $autoCalculatePrice;    

    /**
     * Returns the static model of the specified AR class.
     * @return Inventory the static model class
     */
    public static function model($className=__CLASS__) {
        return parent::model($className);
    }

    /**
     * @return string the associated database table name
     */
    public function tableName() {
        return 'tbl_inventory';
    }

    /**
     * @return array validation rules for model attributes.
     */
    public function rules() {
        // NOTE: you should only define rules for those attributes that
        // will receive user inputs.
        return array(
            array('partno,lastprice,averagecost,baseprice,markupcode', 'required'),
            array('autoCalculatePrice,posthist, nontaxable, equipment, inactive, ismarkup', 'numerical', 'integerOnly' => true),
            array('averagecost, pricea, priceb, pricec, lastprice, buysellr, billrate, wamonths, reorderquanity, reorderlevel, bcost, overhead, frplabor, baseprice', 'numerical'),
            array('partno, cat, subcat', 'length', 'max' => 75),
            array('type, salestype,reworkable', 'length', 'max' => 1),
            array('cunits, markupcode, runits, fr', 'length', 'max' => 2),
            array('costdebit, costcredit, salesdebit, salescredit, bin, epatype, refrtype', 'length', 'max' => 10),
            array('pricebook, vendor, aisle,kind', 'length', 'max' => 4),
            array('equipmenttype', 'length', 'max' => 30),
            array('parttype', 'length', 'max' => 8),
            array('jobclass', 'length', 'max' => 3),
            array('histcode', 'length', 'max' => 36),
            array('notes', 'length', 'max' => 999999999),
            array('mfg', 'length', 'max' => 20),
            array('model', 'length', 'max' => 30),
            array('imagepath', 'length', 'max' => 255),
            array('description', 'safe'),
            array('file', 'file', 'types' => 'jpg,png,jpeg,bmp,gif', 'maxSize' => 1000000, 'allowEmpty' => true),
            array('partno', 'unique', 'className' => 'Inventory', 'attributeName' => 'partno'),
            // The following rule is used by search().
            // Please remove those attributes that should not be searched.
            array('partno, description, type, cat, subcat, averagecost, pricea, priceb, pricec, lastprice, cunits, buysellr, markupcode, runits, costdebit, costcredit, salesdebit, salescredit, bin, pricebook, parttype, posthist, vendor, salestype, notes, billrate, nontaxable, epatype, jobclass, fr, equipment, wamonths, aisle, histcode, mfg, model, reorderquanity, reorderlevel, refrtype, bcost, overhead, frplabor, inactive, ismarkup, imagepath, equipmenttype, baseprice', 'safe', 'on' => 'search'),
        );
    }

    /**
     * @return array relational rules.
     */
    public function relations() {
        // NOTE: you may need to adjust the relation name and the related
        // class name for the relations automatically generated below.
        return array(
            'markupCode' => array(self::BELONGS_TO, 'Markupcodes', 'markupcode'),
            'vendor' => array(self::BELONGS_TO, 'Venderscroll', 'vendor'),
        );
    }

    /**
     * @return array customized attribute labels (name=>label)
     */
    public function attributeLabels() {
        return array(
            'partno' => 'Part number',
            'description' => 'Description',
            'type' => 'Type',
            'cat' => 'Category',
            'subcat' => 'Sub-Category',
            'averagecost' => 'Average Cost',
            'pricea' => 'Price A',
            'priceb' => 'Price B',
            'pricec' => 'Price C',
            'lastprice' => 'Last Purchase',
            'cunits' => 'Cost Units',
            'cunits' => 'Cost Units',
            'buysellr' => 'Buy/Sell Ratio',
            'markupcode' => 'Markup Code',
            'runits' => 'Resale Units',
            'costdebit' => 'Inventory Account',
            'costcredit' => 'Cost Credit',
            'salesdebit' => 'Sales Debit',
            'salescredit' => 'Income Account',
            'bin' => 'Bin',
            'pricebook' => 'Price Book',
            'parttype' => 'Part Type',
            'posthist' => 'Posthist',
            'vendor' => 'Vendor',
            'salestype' => 'Salestype',
            'notes' => 'Notes',
            'billrate' => 'Billrate',
            'nontaxable' => 'Nontaxable',
            'epatype' => 'Epatype',
            'jobclass' => 'Jobclass',
            'fr' => 'Fr',
            'equipment' => 'Equipment',
            'wamonths' => 'Warranty (Months)',
            'aisle' => 'Aisle',
            'histcode' => 'Histcode',
            'mfg' => 'Manufacturer',
            'model' => 'Model',
            'reorderquanity' => 'Reorderquanity',
            'reorderlevel' => 'Reorderlevel',
            'refrtype' => 'Refrtype',
            'bcost' => 'Bcost',
            'overhead' => 'Overhead',
            'frplabor' => 'Frplabor',
            'inactive' => 'Status',
            'ismarkup' => 'Ismarkup',
            'imagepath' => 'Image',
            'equipmenttype' => 'Equipment Type',
            'baseprice' => 'Base price',
            'autoCalculatePrice' => 'Auto Calculate Price',
            'salestype'=>'Sales Type'
        );
    }

    public function getSubcategory() {
        return Invsubcategories::model()->find(array(
                    'alias' => 'sc',
                    'condition' => 'sc.subcategory=:name AND c.categories=:catname',
                    'join' => 'left join tbl_invcategories c on c.id=sc.category',
                    'params' => array(
                        'name' => $this->subcat,
                        'catname' => $this->cat,
                    )
                ));
    }

    public function calculatePrice($model) {
        $oldModel = NULL;
        $oldModel = $this->findByPk($model->partno);

        $oldModel = NULL;
        if (!$model->isNewRecord) {
            $oldModel = $this->findByPk($model->partno);
            //always keep the old number
            $model->partno = $oldModel->partno;
        }

        if ($model->subCategory->applymarkup == 'Average Cost') {
            if ($oldModel != NULL) {


                if ($oldModel->baseprice != $model->baseprice) {

                    $model->averagecost = ($oldModel->baseprice + $model->baseprice) / 2;
                }
            } else {
                $model->averagecost = $model->baseprice;
            }
        } else {
            $model->lastprice = $model->baseprice;
        }
        $subcat = Invsubcategories::model()->find(array(
            'alias' => 'sc',
            'condition' => 'sc.subcategory=:name AND c.categories=:catname',
            'join' => 'left join tbl_invcategories c on c.id=sc.category',
            'params' => array(
                'name' => $model->subcat,
                'catname' => $model->cat,
            )
                ));


        $markupA = Markupcodes::model()->find('markup=:markup AND price="A"', array(
            'markup' => $model->markupcode
                ));

        $markupB = Markupcodes::model()->find('markup=:markup AND price="B"', array(
            'markup' => $model->markupcode
                ));
        $markupC = Markupcodes::model()->find('markup=:markup AND price="C"', array(
            'markup' => $model->markupcode
                ));

        $applymarkup = $subcat->applymarkup;
        $rs = array();
        if ($applymarkup == 'Average Cost') {
            if ($model->averagecost > $markupA->morethan && $model->averagecost < $markupA->lessthan) {
                $rs['pricea'] = $model->averagecost * $markupA->cost / 100;
            }


            if ($model->averagecost > $markupB->morethan && $model->averagecost < $markupB->lessthan) {
                $rs['priceb'] = $model->averagecost * $markupB->cost / 100;
            }

            if ($model->averagecost > $markupC->morethan && $model->averagecost < $markupC->lessthan) {
                $rs['pricec'] = $model->averagecost * $markupC->cost / 100;
            }
        } else {
            if ($model->lastprice > $markupA->morethan && $model->lastprice < $markupA->lessthan) {
                $rs['pricea'] = $model->baseprice * $markupA->cost / 100;
            }


            if ($model->lastprice > $markupB->morethan && $model->lastprice < $markupB->lessthan) {
                $rs['priceb'] = $model->baseprice * $markupB->cost / 100;
            }

            if ($model->lastprice > $markupC->morethan && $model->lastprice < $markupC->lessthan) {
                $rs['pricec'] = $model->baseprice * $markupC->cost / 100;
            }
        }
        $rs['pricea'] = number_format($rs['pricea'], 2);
        $rs['priceb'] = number_format($rs['priceb'], 2);
        $rs['pricec'] = number_format($rs['pricec'], 2);
        return $rs;
    }

    public function beforeSave() {


        //find out the average cost
        //each time baseprice changed we need to update average cost
        $oldModel = NULL;
        $defaultAccount = Defaultaccounts::getAccount('Inventory Account');
        if ($this->salescredit == NULL) {
            $this->salescredit = $defaultAccount;
        }
        if ($this->costdebit == NULL) {
            $this->costdebit = $defaultAccount;
        }
        if (!$this->isNewRecord) {
            $oldModel = $this->findByPk($this->partno);
            //always keep the old number
            $this->partno = $oldModel->partno;
        }

        if ($this->subCategory!=null && $this->subCategory->applymarkup == 'Average Cost') {
            if ($oldModel != NULL) {


                if ($oldModel->baseprice != $this->baseprice) {

                    $this->averagecost = ($oldModel->baseprice + $this->baseprice) / 2;
                }
            } else {
                $this->averagecost = $this->baseprice;
            }
        } else {
            $this->lastprice = $this->baseprice;
        }


        $this->notes = trim($this->notes);

        $this->file = CUploadedFile::getInstance($this, 'file');
        if ($this->file != NULL) {
            //made a random name base on timestamp
            $ext = getFileExt($this->file);
            $name = getFileBodyName($this->file);


            $this->imagepath = $name . '_' . time() . $ext;

            $this->file->saveAs('images/content/inventory/' . $this->imagepath, true);
        }

        return parent::beforeSave();
    }

    public function init() {
        parent::init();
        $this->cunits = 'EA';
        $this->buysellr = 1;
    }

    public function getCategoryList() {
        $data = Yii::app()->db->createCommand('select * from tbl_invcategories where status=0')->queryAll();
        $rs = array();
        if (count($data) > 0) {
            foreach ($data as $item) {
                $rs[$item['categories']] = $item['categories'];
            }
        }
        return $rs;
    }

    public function getSubCategoryList($cat=NULL) {
        if ($cat == NULL && $this->cat != NULL) {
            $cat = $this->cat;
        }
        $data = Yii::app()->db->createCommand('
                select sc.* from tbl_invsubcategories sc
                join tbl_invcategories c on sc.category=c.id
                where sc.status=0 AND c.categories=:category')->bindValue('category', $cat)->queryAll();
        $rs = array();
        if (count($data) > 0) {
            foreach ($data as $item) {
                $rs[$item['subcategory']] = $item['subcategory'];
            }
        }
        return $rs;
    }

//        public function getSubCategoryText(){
//            $data=Yii::app()->db->createCommand('select subcategory from tbl_invsubcategories where status=1 AND id=:id')->bindValue('id',$this->subcat)->queryScalar();
//            if($data!==false) return $data;
//        }
    public function getStatusList() {
        return array(
            -1 => 'Inactive',
            0 => 'Active'
        );
    }

    public function getStatusText() {
        return isset($this->statusList[$this->inactive]) ? $this->statusList[$this->inactive] : NULL;
    }
    public function getTypeList(){
        return array(
            'I'=>'Inventory',
            'B'=>'Billing Code',
            'P'=>'History'              
        );
    }
    public function getPartTypeList() {
        return array(
            'Stock' => 'Stock',
            'Serial' => 'Serial',
            'Assembly' => 'Assembly'
        );
    }

    public function getApplyMakupList() {
        return array(
            'Average Cost' => 'Average Cost',
            'Base Cost' => 'Base Cost'
        );
    }

    public function getApplyMakupText() {
        return isset($this->applyMakupList[$this->applymarkup]) ? $this->applyMakupList[$this->applymarkup] : NULL;
    }

    public function getMarkupCodeList() {
        $codes = Markupcodes::model()->findAll();
        $rs = array(
            '' => 'N/A'
        );
        foreach ($codes as $item) {
            $rs[$item->markup] = $item->markup;
        }
        return $rs;
    }

    public function getMarkupText() {
        $markup = Markupcodes::model()->find('id=:id', array(
            'id' => $this->markupcode
                ));
        return $markup->markup;
    }

    public function getPriceBookList() {
        $pricebook = Pricebooks::model()->findAll();
        $rs = array(
            'All' => 'All'
        );

        foreach ($pricebook as $item) {
            $rs[$item->book] = $item->bookdesc;
        }
        return $rs;
    }

    public function getMarkupValue($type, $code=NULL) {
        if ($code == NULL) {
            $code = $this->markupcode;
        }

        $markup = Markupcodes::model()->find('markup=:code AND price=:price', array(
            'code' => $code,
            'price' => $type
                ));
        if ($markup != NULL)
            return $markup->cost;
        else
            return NULL;
    }

    public function getPriceBookText() {
        $priceBook = Pricebooks::model()->find('book=:book', array(
            'book' => $this->pricebook
                ));
        if ($priceBook != NULL) {
            return $priceBook->bookdesc;
        } else {
            return $this->pricebook;
        }
    }

    public function getVendorList() {



        $ventorList = Yii::app()->db->createCommand('select * from tbl_vendors')->queryAll();
        $rs = array();
        if (count($ventorList) > 0) {
            foreach ($ventorList as $item) {
                $rs[$item['vendor']] = $item['company'];
            }
        }
        return $rs;
    }

    public function getVendorText($vendor=NULL) {
        if ($vendor == NULL) {
            $vendor = $this->vendor;
        }
        $vendor = Yii::app()->db->createCommand('select company,firstname from tbl_vendors where vendor=:vendor')->bindValue('vendor', $vendor)->queryRow();

        if ($vendor) {


            if ($vendor != false) {

                return $vendor['company'];
            }
        }
    }

    public function isTaxableByZipcodeAndState($zipcode,$state){
        $tax = Yii::app()->db->createCommand('select labor,material,sacontracts,shippingtaxable from tbl_taxcode where zipcode=:zipcode and state = :state')
                            ->bindValues(array(
                                'zipcode' => $zipcode,
                                'state'=>$state)
                                    )->queryRow();
            
            if ($tax == NULL) {
                return false;
            }

            switch ($this->salestype) {
                case 'L':
                    if ($tax['labor'] == 1) {
                        return true;
                    }
                    break;
                case 'M':
                    if ($tax['material'] == 1) {
                        return true;
                    }
                    break;
                case 'S':
                    if ($tax['shippingtaxable'] == 1) {
                        return true;
                    }
                    break;
                case 'C':
                case 'A':
                    if ($tax['shippingtaxable'] == 1) {
                        return true;
                    }
                    break;
                default:
                    return false;
            }
    }
    public function isTaxable($custno) {
       
        $customer=Yii::app()->db->createCommand('select zip,state from tbl_customers where custno=:custno')->bindValues(array('custno'=>$custno))->queryRow();
        
        if($customer!==false && $customer!==NULL){
            $tax = Yii::app()->db->createCommand('select labor,material,sacontracts,shippingtaxable from tbl_taxcode where zipcode=:zipcode and state = :state')
                            ->bindValues(array(
                                'zipcode' => $customer['zip'],
                                'state'=>$customer['state'])
                                    )->queryRow();
            
            if ($tax == NULL) {
                return false;
            }           
            switch ($this->salestype) {
                case 'L':
                    if ($tax['labor'] == 1) {
                        return true;
                    }
                    break;
                case 'M':
                    if ($tax['material'] == 1) {
                        return true;
                    }
                    break;
                case 'S':
                    if ($tax['shippingtaxable'] == 1) {
                        return true;
                    }
                    break;
                case 'C':
                case 'A':
                    if ($tax['shippingtaxable'] == 1) {
                        return true;
                    }
                    break;
                default:
                    return false;
            }
        }
        return false;
    }

    public function getPrice($pricecode=null) {
        
        if ($this->type == 'B') {
            return $this->billrate;
        } else {
            switch (strtolower($pricecode)) {
                case 'a':
                    return $this->pricea;
                    break;
                case 'b':
                    return $this->priceb;
                    break;
                case 'c':
                    return $this->pricec;
                    break;
            }
        }
    }

    public function getCost() {
        if ($this->subCategory != NULL && $this->subCategory->applymarkup == 'Average Cost') {
            return $this->averagecost;
        } else {
            return $this->baseprice;
        }
    }
    
    public function getSalesTypeList(){
        return array(
            'M'=>'Material',
            'L'=>'Labor',            
            'S'=>'Shipping',
            'C'=>'Contract',
        );
    }
    public static function getExistingPartForPrimaryPart($partno)
    {
        $existingModelU =  Inventory::model()->findByPk($partno."/R");
        $existingModelR =  Inventory::model()->findByPk($partno."/U");
        return array('/U'=>$existingModelU,'/R'=>$existingModelR);
    }
    public static function getPartKinds(){
        return array(
            self::PART_NEW    =>  'New',
            self::PART_REFURB =>  'Reburbished',
            self::PART_USED   =>  'Used',
            );
    }
    const PART_NEW = 0;
    const PART_REFURB = 1;
    const PART_USED = 2;
}